package com.central.apps.controller;

import com.central.app.service.HandlerService;
import com.central.common.exception.service.ServiceException;
import com.central.common.web.Result;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author mengweitao
 * @Date 2023/3/3/003 15:04
 * 出入库
 */
@RestController
@RequestMapping("/IntoOutStore")
public class IntoOutStoreController {
    private final static String XLS = "xls";
    private final static String XLSX = "xlsx";

    @Autowired
    private HandlerService handlerService;
    /**
     * 入库子表模板
     * @param response
     * @throws IOException
     */
    @PostMapping("/intoExportExcel")
    public void intoExportExcel(HttpServletResponse response) throws IOException {
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("Sheet1");
            HSSFRow row = sheet.createRow(0);

            HSSFFont redFont = wb.createFont();
            redFont.setColor(Font.COLOR_RED);
            HSSFCellStyle style = wb.createCellStyle();
            style.setFont(redFont);
            style.setWrapText(true);
            style.setAlignment(HorizontalAlignment.LEFT);// 左右居中
            style.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中

            HSSFCell cell = row.createCell(0);
            cell.setCellValue("物料编码");
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellStyle(style);
            cell1.setCellValue("*物料名称");
            HSSFCell cell2 = row.createCell(2);
            cell2.setCellStyle(style);
            cell2.setCellValue("*型号");
            HSSFCell cell3 = row.createCell(3);
            cell3.setCellValue("规格");
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellStyle(style);
            cell4.setCellValue("*数量");
            HSSFCell cell8 = row.createCell(5);
            cell8.setCellStyle(style);
            cell8.setCellValue("*单价");
            HSSFCell cell5 = row.createCell(6);
            cell5.setCellValue("库位编号");
            HSSFCell cell6 = row.createCell(7);
            cell6.setCellValue("库位名称");
            HSSFCell cell7 = row.createCell(8);
            cell7.setCellValue("入库日期");
            HSSFCell cell9 = row.createCell(9);
            cell9.setCellValue("是否专用");

            String zy = "zy";   //是否DPA分析
            Map<String, String> zyMap = new HashMap<>();
            zyMap.put("zy","是,否");
            HSSFDataValidation dataValidation8 = createBox(zy, zyMap, 1, 1000, 9, 9);
            if (dataValidation8 != null) {
                sheet.addValidationData(dataValidation8);
            }
            //输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            String exportFileName ="入库申请物料行.xls";
            String fileNameURL = URLEncoder.encode(exportFileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+fileNameURL+";"+"filename*=utf-8''"+fileNameURL);
            response.setContentType("application/x-xls");
            wb.write(output);
            output.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 导入
     */
    @PostMapping("/inquiryImportExcel")
    @Transactional(rollbackFor = Exception.class)
    public Result inquiryImportExcel(@RequestParam("file") MultipartFile myfile, @RequestParam("id") String id) throws Exception {
        try {
            List<Map<String, Object>> bySql = handlerService.findBySql("select warehousenum,instonum from gq_instorage where id = '" + id + "'");
            String warehousenum = MapUtils.getString(bySql.get(0), "warehousenum");
            String instonum = MapUtils.getString(bySql.get(0), "instonum");
            Workbook workbook = null;
            String fileName = myfile.getOriginalFilename();
            if (fileName.endsWith(XLS)) {//获取文件后缀
                workbook = new HSSFWorkbook(myfile.getInputStream());
            } else if (fileName.endsWith(XLSX)) {
                workbook = new XSSFWorkbook(myfile.getInputStream());
            } else {
                throw new Exception("文件不是excel文件");
            }

            Sheet sheet1 = workbook.getSheet("Sheet1");
            int rows = sheet1.getLastRowNum();
            if (rows == 0) {
                throw new Exception("请填写数据");
            }

            List<Map<String,Object>> itemList = new ArrayList<>();

            for (int i = 0; i <= rows; i++) {
                Map<String, Object> resultMap = new HashMap<>();
                List<Map<String, Object>> itemListMap = new ArrayList<>();
                //读取左上端单元格
                Row row = sheet1.getRow(i);
                if (row != null) {
                    String itemnum = getCellValue(row, 0).toString().trim();//物料编码
                    String itemdesc = getCellValue(row, 1).toString().trim();//物料名称
                    String model = getCellValue(row, 2).toString().trim();//型号
                    String specifications = getCellValue(row, 3).toString().trim();//规格
                    String innum = getCellValue(row, 4).toString().trim();//数量
                    String unitcost = getCellValue(row, 5).toString().trim();//单价
                    String shelfnum = getCellValue(row, 6).toString().trim();//库位编号
                    String shelfdesc = getCellValue(row, 7).toString().trim();//库位名称
                    String reqdeliverydate = getCellValue(row, 8).toString().trim();//入库日期
                    String occupnot = getCellValue(row, 9).toString().trim();//是否专用

                    if(i==0){
                        if(!"物料编码".equals(itemnum)||!"*物料名称".equals(itemdesc)||!"*型号".equals(model)||!"规格".equals(specifications)
                        ||!"*数量".equals(innum)||!"*单价".equals(unitcost)||!"库位编号".equals(shelfnum)||!"库位名称".equals(shelfdesc)
                        ||!"入库日期".equals(reqdeliverydate)||!"是否专用".equals(occupnot)){
                            throw new ServiceException("导入的模板错误，请下载模板进行导入");
                        }
                    }else{
                        if(StringUtils.isBlank(itemdesc)||StringUtils.isBlank(model)||StringUtils.isBlank(innum)||StringUtils.isBlank(unitcost)){
                            int line = i+1;
                            throw new ServiceException("第"+line+"行有必填字段未填写，填写后再进行导入！");
                        }

                        //itemnum不为null  所有物料相关信息根据编码来
                        if(StringUtils.isNotBlank(itemnum)){
                            itemListMap = handlerService.findBySql("select * from gq_item where itemnum = '" + itemnum + "'");
                            if(CollectionUtils.isEmpty(itemListMap)){
                                int line = i+1;
                                throw new ServiceException("第"+line+"行输入的物料编码系统中不存在，请修改后重新导入！");
                            }
                        }else{
                            StringBuilder stb = new StringBuilder();
                            String sql = "select * from gq_item where description = '" + itemdesc + "' and model = '" + model + "'";
                            stb.append(sql);
                            if(StringUtils.isNotBlank(specifications)){
                                stb.append(" and specifications = '" + specifications + "'");
                            }
                            itemListMap = handlerService.findBySql(stb.toString());
                        }

                        if(CollectionUtils.isEmpty(itemListMap)){
                            int line = i+1;
                            throw new ServiceException("第"+line+"行输入的物料名称和规格型号系统中不存在，请修改后重新导入！");
                        }

                        if(StringUtils.isNotBlank(itemnum)){
                            resultMap.put("itemnum",itemnum);
                        }else{
                            resultMap.put("itemnum",MapUtils.getString(itemListMap.get(0),"itemnum"));
                        }

                        resultMap.put("itemdesc",MapUtils.getString(itemListMap.get(0),"description"));
                        resultMap.put("model",MapUtils.getString(itemListMap.get(0),"model"));
                        resultMap.put("manufacturer",MapUtils.getString(itemListMap.get(0),"manufacturer"));
                        resultMap.put("brand",MapUtils.getString(itemListMap.get(0),"spemodel"));
                        resultMap.put("orderunit",MapUtils.getString(itemListMap.get(0),"unit"));
                        resultMap.put("unitcost",unitcost);
                        resultMap.put("innum",innum);
                        resultMap.put("request",innum);
                        BigDecimal unitcostBig = new BigDecimal(unitcost);
                        BigDecimal innumBig = new BigDecimal(innum);
                        resultMap.put("linecost",unitcostBig.multiply(innumBig).toString());
                        if(StringUtils.isNotBlank(reqdeliverydate)){
                            Cell cell = row.getCell(8);
                            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                            if (cell != null) {
                                int line = i+1;
                                try {
                                    double numericCellValue = cell.getNumericCellValue();
                                    Date date = DateUtil.getJavaDate(numericCellValue);
//                                Date date = cell.getDateCellValue();
                                    String format1 = format.format(date);
                                    resultMap.put("reqdeliverydate", format1);//入库日期
                                }catch (Exception e){
                                    throw new ServiceException("第"+line+"行输入的入库日期格式不正确，应设置为单元格格式为yyyy/m/d");
//                                    return Result.failed("第"+line+"行输入的到货日期格式不正确，应设置为单元格格式为yyyy/m/d");
                                }
                            }else{
                                Date date = new Date();
                                String format1 = format.format(date);
                                resultMap.put("reqdeliverydate", format1);//入库日期
                            }
                        }

                        String storesql="select * from gq_areashelf where storenum = '" + warehousenum + "'";
                        StringBuilder sbl1 = new StringBuilder();
                        sbl1.append(storesql);
                        if(StringUtils.isNotBlank(shelfnum)){
                            sbl1.append(" and shelfnum = '" + shelfnum + "'");
                        }
                        if(StringUtils.isNotBlank(shelfdesc)){
                            sbl1.append(" and shelfdesc = '" + shelfdesc + "'");
                        }

                        List<Map<String, Object>> bySql1 = handlerService.findBySql(sbl1.toString());
                        if(CollectionUtils.isEmpty(bySql1)){
                            int line = i+1;
                            throw new ServiceException("第"+line+"行输入的库位信息不属于"+warehousenum+"库房下，请修改后重新导入！");
                        }
                        resultMap.put("binnum",MapUtils.getString(bySql1.get(0),"shelfnum"));
                        resultMap.put("storageroom",warehousenum);
                        resultMap.put("instonum",instonum);
                        resultMap.put("occupnot",occupnot);
                        handlerService.addBizNum("instorageApp","gq_instoline",resultMap);
                        itemList.add(resultMap);
                        handlerService.insert("gq_instoline",resultMap);
                    }
                }
            }
            return Result.succeed("入库申请物料行成功导入"+itemList.size()+"条。");
        }catch (Exception e){
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return Result.failed(e.getMessage());
        }
    }

    /**
     * 获取Cell内容
     */
    public Object getCellValue(Row row, int column) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {

                if (cell.getCellTypeEnum()==CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.#########");
                    String cellValueStr = String.valueOf(val);
                    val = df.format(Double.valueOf(cellValueStr));
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }

    public HSSFDataValidation createBox(String col, Map<String, String> boxMap, int firstRow, int lastRow, int firstCol, int lastCol) {
        HSSFDataValidation dataValidation = null;
        //查询码值表
        String cols = "";
        if (null != boxMap.get(col)) {
            cols = boxMap.get(col);
        }
        //设置下拉框
        if (cols.length() > 0 && null != cols) {
            String str[] = cols.split(",");
            CellRangeAddressList cas = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            //创建下拉数据列
            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(str);
            //将下拉数据放入下拉框
            dataValidation = new HSSFDataValidation(cas, dvConstraint);
        }
        return dataValidation;
    }

}
